﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MJM.SPP.EntidadesNegocio;
using MJM.CBD.Conn;

namespace MJM.SPP.AccesoDatos
{
    public class ColumCodigoAD
    {
        public int Insertar(ColumCodigoEN obj)
        {
            SqlConnection conn=new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_i_T_WEB_CODCOL_Insertar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            int codigo=0;
            SqlParameter prmID, prmAsig, prmFecha, prmCol1, prmCol2, prmCol3, prmCol4, prmCol5, prmCol6, prmCol7, prmCol8, prmCol9, prmCol10;
            try
            {
                prmID = cmd.CreateParameter();
                prmID.Direction = ParameterDirection.ReturnValue;
                prmID.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(prmID);

                prmAsig = cmd.CreateParameter();
                prmAsig.ParameterName = "@vASITAR_P_inID";
                prmAsig.SqlDbType = SqlDbType.Int;
                prmAsig.Value = obj.ASITAR_P_inID;
                cmd.Parameters.Add(prmAsig);

                prmFecha = cmd.CreateParameter();
                prmFecha.ParameterName = "@vCODCOL_chFECREG";
                prmFecha.SqlDbType = SqlDbType.VarChar;
                prmFecha.Value = obj.CODCOL_chFECREG;
                cmd.Parameters.Add(prmFecha);

                prmCol1 = cmd.CreateParameter();
                prmCol1.ParameterName = "@vCODCOL_inCOLA";
                prmCol1.SqlDbType = SqlDbType.Int;
                prmCol1.Value = obj.CODCOL_inCOLA;
                cmd.Parameters.Add(prmCol1);

                prmCol2 = cmd.CreateParameter();
                prmCol2.ParameterName = "@vCODCOL_inCOLB";
                prmCol2.SqlDbType = SqlDbType.Int;
                prmCol2.Value = obj.CODCOL_inCOLB;
                cmd.Parameters.Add(prmCol2);

                prmCol3 = cmd.CreateParameter();
                prmCol3.ParameterName = "@vCODCOL_inCOLC";
                prmCol3.SqlDbType = SqlDbType.Int;
                prmCol3.Value = obj.CODCOL_inCOLC;
                cmd.Parameters.Add(prmCol3);

                prmCol4 = cmd.CreateParameter();
                prmCol4.ParameterName = "@vCODCOL_inCOLD";
                prmCol4.SqlDbType = SqlDbType.Int;
                prmCol4.Value = obj.CODCOL_inCOLD;
                cmd.Parameters.Add(prmCol4);

                prmCol5 = cmd.CreateParameter();
                prmCol5.ParameterName = "@vCODCOL_inCOLE";
                prmCol5.SqlDbType = SqlDbType.Int;
                prmCol5.Value = obj.CODCOL_inCOLE;
                cmd.Parameters.Add(prmCol5);

                prmCol6 = cmd.CreateParameter();
                prmCol6.ParameterName = "@vCODCOL_inCOLF";
                prmCol6.SqlDbType = SqlDbType.Int;
                prmCol6.Value = obj.CODCOL_inCOLF;
                cmd.Parameters.Add(prmCol6);

                prmCol7 = cmd.CreateParameter();
                prmCol7.ParameterName = "@vCODCOL_inCOLG";
                prmCol7.SqlDbType = SqlDbType.Int;
                prmCol7.Value = obj.CODCOL_inCOLG;
                cmd.Parameters.Add(prmCol7);

                prmCol8 = cmd.CreateParameter();
                prmCol8.ParameterName = "@vCODCOL_inCOLH";
                prmCol8.SqlDbType = SqlDbType.Int;
                prmCol8.Value = obj.CODCOL_inCOLH;
                cmd.Parameters.Add(prmCol8);

                prmCol9 = cmd.CreateParameter();
                prmCol9.ParameterName = "@vCODCOL_inCOLI";
                prmCol9.SqlDbType = SqlDbType.Int;
                prmCol9.Value = obj.CODCOL_inCOLI;
                cmd.Parameters.Add(prmCol9);

                prmCol10 = cmd.CreateParameter();
                prmCol10.ParameterName = "@vCODCOL_inCOLJ";
                prmCol10.SqlDbType = SqlDbType.Int;
                prmCol10.Value = obj.CODCOL_inCOLJ;
                cmd.Parameters.Add(prmCol10);

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                codigo = Convert.ToInt32(prmID.Value);

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return 0;
            }
            return codigo;
        }

        public List<ColumCodigoEN> Listar(String Usuario)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_WEB_CODCOL_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmUsuario,prmCodigo;
            SqlDataReader dr;
            List<ColumCodigoEN> lst = new List<ColumCodigoEN>();
            ColumCodigoEN obj;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                prmUsuario = cmd.CreateParameter();
                prmUsuario.ParameterName = "@vUSUPAD_P_chUSU";
                prmUsuario.SqlDbType = SqlDbType.VarChar;
                prmUsuario.Value = Usuario;
                cmd.Parameters.Add(prmUsuario);

                prmCodigo = cmd.CreateParameter();
                prmCodigo.ParameterName = "@vASITAR_chCODTAR";
                prmCodigo.SqlDbType = SqlDbType.VarChar;
                prmCodigo.Value = "";
                cmd.Parameters.Add(prmCodigo);

                cmd.Connection.Open();
                da.SelectCommand = cmd;
                da.Fill(ds);
                string nombre = "D:\\Desarrollo\\Dropbox\\ProyPadron\\Matriz.xml";
              //  ds.WriteXmlSchema(nombre);
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    obj = new ColumCodigoEN();
                    obj.CODCOL_P_inID = dr.GetInt32(0);
                    obj.ASITAR_P_inID = dr.GetInt32(1);
                    obj.CODCOL_inCOLA = dr.GetInt32(2);
                    obj.CODCOL_inCOLB = dr.GetInt32(3);
                    obj.CODCOL_inCOLC = dr.GetInt32(4);
                    obj.CODCOL_inCOLD = dr.GetInt32(5);
                    obj.CODCOL_inCOLE = dr.GetInt32(6);
                    obj.CODCOL_inCOLF = dr.GetInt32(7);
                    obj.CODCOL_inCOLG = dr.GetInt32(8);
                    obj.CODCOL_inCOLH = dr.GetInt32(9);
                    obj.CODCOL_inCOLI = dr.GetInt32(10);
                    obj.CODCOL_inCOLJ = dr.GetInt32(11);
                    obj.CODCOL_chFECREG = dr.GetString(12);
                    obj.CODCOL_inFLAELI = dr.GetInt32(13);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return null;
            }
            return lst;
        }

        public List<ColumCodigoEN> ListarxCodigo(String Codigo)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_WEB_CODCOL_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmUsuario, prmCodigo;
            SqlDataReader dr;
            List<ColumCodigoEN> lst = new List<ColumCodigoEN>();
            ColumCodigoEN obj;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                prmUsuario = cmd.CreateParameter();
                prmUsuario.ParameterName = "@vUSUPAD_P_chUSU";
                prmUsuario.SqlDbType = SqlDbType.VarChar;
                prmUsuario.Value = "";
                cmd.Parameters.Add(prmUsuario);

                prmCodigo = cmd.CreateParameter();
                prmCodigo.ParameterName = "@vASITAR_chCODTAR";
                prmCodigo.SqlDbType = SqlDbType.VarChar;
                prmCodigo.Value = Codigo;
                cmd.Parameters.Add(prmCodigo);

                cmd.Connection.Open();
                da.SelectCommand = cmd;
             //   da.Fill(ds);
             //   string nombre = "D:\\Desarrollo\\Dropbox\\ProyPadron\\Matriz.xml";
                //ds.WriteXmlSchema(nombre);
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    obj = new ColumCodigoEN();
                    obj.CODCOL_P_inID = dr.GetInt32(0);
                    obj.ASITAR_P_inID = dr.GetInt32(1);
                    obj.CODCOL_inCOLA = dr.GetInt32(2);
                    obj.CODCOL_inCOLB = dr.GetInt32(3);
                    obj.CODCOL_inCOLC = dr.GetInt32(4);
                    obj.CODCOL_inCOLD = dr.GetInt32(5);
                    obj.CODCOL_inCOLE = dr.GetInt32(6);
                    obj.CODCOL_inCOLF = dr.GetInt32(7);
                    obj.CODCOL_inCOLG = dr.GetInt32(8);
                    obj.CODCOL_inCOLH = dr.GetInt32(9);
                    obj.CODCOL_inCOLI = dr.GetInt32(10);
                    obj.CODCOL_inCOLJ = dr.GetInt32(11);
                    obj.CODCOL_chFECREG = dr.GetString(12);
                    obj.CODCOL_inFLAELI = dr.GetInt32(13);
                    obj.ASITAR_chCODTAR = dr.GetString(14);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                return null;
            }
            return lst;
        }        
    }
}
